package com.topisv.tms.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.topisv.tms.costants.Constants;
import com.topisv.tms.entity.dto.ExcelDto;
import com.topisv.tms.utils.ColorUtil;
import com.topisv.tms.utils.InitProperties;
import com.topisv.tms.web.lpn.vo.PalletInfoVo;

/**
 * Class: exportUtil.java Description: 导出工具类
 * 
 * @author   caomingwu
 */
public class ExcelUtils3 {
    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils3.class);
    
    public final static String TEMPPATH = InitProperties.get("excel.download.path");
    
    /**
     * 装箱单查询页面导出_进入
     * @param title
     * @param filepath
     * @param title_page
     * @param sheetname
     * @param list
     * @param date
     * @param username
     */
    public static void writeExcel(String[] title, String filepath, String title_page, String sheetname,
        List<Object[]> list,String date,String username)
    {
        OutputStream os = null;
        try {
            os = new FileOutputStream(TEMPPATH + filepath);
            writeExcel(title, title_page, sheetname, list, os,date,username);
        } catch (Exception e) {
            logger.error("writeExcel case some error.", e);
        } finally {
            if (null != os) {
                try {
                    os.close();
                    os = null;
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    /**
     * 装箱单操作页面导出_进入
     * @param title
     * @param filepath
     * @param title_page
     * @param sheetname
     * @param list
     * @param date
     * @param username
     */
    public static void writeExcel(String[] title, String filepath, String title_page, String sheetname,
        List<Object[]> list,PalletInfoVo palletInfoVo)
    {
        OutputStream os = null;
        try {
            os = new FileOutputStream(TEMPPATH + filepath);
            writeExcel(title, title_page, sheetname, list, os,palletInfoVo);
        } catch (Exception e) {
            logger.error("writeExcel case some error.", e);
        } finally {
            if (null != os) {
                try {
                    os.close();
                    os = null;
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    /**
     * 装箱单查询页面导出_操作
     * @param title
     * @param title_page
     * @param sheetname
     * @param list
     * @param os
     * @param date
     * @param username
     */
    public static void writeExcel(String[] title, String title_page, String sheetname, List<Object[]> list,
        OutputStream os,String date,String username)
    {
        WritableWorkbook wwb = null;
        
        try {
            wwb = Workbook.createWorkbook(os);
            
            // 添加工作表，并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet(sheetname, 0);
            
            // 页标题行
            WritableFont font_pageTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                13, // 字号
                WritableFont.NO_BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            int startRow = 0;
            if (!StringUtils.isBlank(title_page)) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                
                sheet.addCell(new Label(0, 0, title_page, fromat_pageTitle)); // 合并单元格
                sheet.mergeCells(0, 0, title.length - 1, 0); // 合并单元格
                startRow++;
            }
            sheet.setRowView(0, 800); // 设置行的高度
            if (!StringUtils.isBlank(date)) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.mergeCells(0, 1, 9, 1); // 合并单元格
                sheet.addCell(new Label(0, 1, "导出时间:"+date, fromat_pageTitle)); // 合并单元格
            }
            if (!StringUtils.isBlank(username)) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(10, 1, "操作人:"+username, fromat_pageTitle)); // 合并单元格
                //sheet.mergeCells(6, 1, 4, 1); // 合并单元格
                
                startRow++;
            }
            /*
             * mergeCells(a,b,c,d) 单元格合并函数 a:单元格的列号 ;b:单元格的行号; c:从单元格[a,b]起,向下合并的列数; d:从单元格[a,b]起，向下合并的行数
             */
            // 栏目标题行
            WritableFont font_columnTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                WritableFont.DEFAULT_POINT_SIZE, // 字号
                WritableFont.BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            
            WritableCellFormat fromat_columnTitle = new WritableCellFormat(font_columnTitle);
            fromat_columnTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            fromat_columnTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            fromat_columnTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            fromat_columnTitle.setBackground(ColorUtil.getNearestColour("#66CCFF")); // 设置单元格背景颜色
            
            for (int i = 0; i < title.length; i++) {
                Label label = new Label(i, startRow, title[i], fromat_columnTitle);
                sheet.addCell(label);
                
                sheet.setColumnView(i, 15); // 设置列的宽度
                sheet.setRowView(1, 400); // 设置行的高度
            }
            // 表格数据行
            
            // 单元格格式1——正常字体
            WritableCellFormat format_cell_1 = new WritableCellFormat();
            format_cell_1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            
            // 单元格格式2 ——红色背景
            WritableCellFormat format_cell_2 = new WritableCellFormat();
            format_cell_2.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            format_cell_2.setBackground(Colour.RED);
            
            if (list != null && list.size() > 0) {
                
                for (int i = 0; i < list.size(); i++) {
                    
                    Object[] obj = (Object[])list.get(i);
                    for (int j = 0; j < obj.length - 1; j++) {
                        String value = (obj[j] == null ? "" : obj[j].toString());
                        Label label = null;
                        if (obj[obj.length - 1] != null) {
                            label = new Label(j, i + startRow + 1, value, format_cell_2);
                        } else {
                            label = new Label(j, i + startRow + 1, value, format_cell_1);
                        }
                        sheet.addCell(label);
                    }
                    sheet.setRowView(i + 2, 400); // 设置行的高度
                    
                }
                
            }
            wwb.write(); // 写入数据
        } catch (Exception e) {
            logger.error("ExcelUtils writeExcel case some error.", e);
        } finally {
            if (null != wwb) {
                try {
                    wwb.close();
                    wwb = null;
                } catch (WriteException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    /**
     * 装箱单操作页面导出_操作
     * @param title
     * @param title_page
     * @param sheetname
     * @param list
     * @param os
     * @param date
     * @param username
     */
    public static void writeExcel(String[] title, String title_page, String sheetname, List<Object[]> list,
        OutputStream os,PalletInfoVo palletInfoVo)
    {
        WritableWorkbook wwb = null;
        
        try {
            wwb = Workbook.createWorkbook(os);
            
            // 添加工作表，并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet(sheetname, 0);
            
            // 页标题行
            WritableFont font_pageTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                13, // 字号
                WritableFont.NO_BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            int startRow = 0;
            if (!StringUtils.isBlank(title_page)) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                
                sheet.addCell(new Label(0, 0, title_page, fromat_pageTitle)); // 合并单元格
                sheet.mergeCells(0, 0, title.length - 1, 0); // 合并单元格
                startRow++;
            }
            sheet.setRowView(0, 800); // 设置行的高度
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (!StringUtils.isBlank(palletInfoVo.getLpnNo())) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(0, 1, "LPN编码:"+palletInfoVo.getLpnNo(), fromat_pageTitle)); // 合并单元格
            }
            if (!StringUtils.isBlank(palletInfoVo.getWarehouseCode())) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(2, 1, "仓库:"+palletInfoVo.getWarehouseCode(), fromat_pageTitle)); // 合并单元格
            }
            if (!StringUtils.isBlank(palletInfoVo.getName())) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(4, 1, "托盘名称:"+palletInfoVo.getName(), fromat_pageTitle)); // 合并单元格
            }
            if (!StringUtils.isBlank(palletInfoVo.getPalletNum())) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(6, 1, "托盘编码:"+palletInfoVo.getPalletNum(), fromat_pageTitle)); // 合并单元格
                startRow++;
            }
            if (!StringUtils.isBlank(sdf.format(new Date()))) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                //sheet.mergeCells(0, 1, 9, 1); // 合并单元格
                sheet.addCell(new Label(0, list.size()+2, "导出时间:"+sdf.format(palletInfoVo.getCreateTime()), fromat_pageTitle)); // 合并单元格
            }
            if (!StringUtils.isBlank(palletInfoVo.getUsername())) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.LEFT); // 设置文字靠左
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                sheet.addCell(new Label(2, list.size()+2, "操作人:"+palletInfoVo.getUsername(), fromat_pageTitle)); // 合并单元格
                //sheet.mergeCells(6, 1, 4, 1); // 合并单元格
                startRow++;
            }
            /*
             * mergeCells(a,b,c,d) 单元格合并函数 a:单元格的列号 ;b:单元格的行号; c:从单元格[a,b]起,向下合并的列数; d:从单元格[a,b]起，向下合并的行数
             */
            // 栏目标题行
            WritableFont font_columnTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                WritableFont.DEFAULT_POINT_SIZE, // 字号
                WritableFont.BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            
            WritableCellFormat fromat_columnTitle = new WritableCellFormat(font_columnTitle);
            fromat_columnTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            fromat_columnTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            fromat_columnTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            fromat_columnTitle.setBackground(ColorUtil.getNearestColour("#66CCFF")); // 设置单元格背景颜色
            
            for (int i = 0; i < title.length; i++) {
                Label label = new Label(i, startRow, title[i], fromat_columnTitle);
                sheet.addCell(label);
                
                sheet.setColumnView(i, 15); // 设置列的宽度
                sheet.setRowView(1, 400); // 设置行的高度
            }
            // 表格数据行
            
            // 单元格格式1——正常字体
            WritableCellFormat format_cell_1 = new WritableCellFormat();
            format_cell_1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            
            // 单元格格式2 ——红色背景
            WritableCellFormat format_cell_2 = new WritableCellFormat();
            format_cell_2.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            format_cell_2.setBackground(Colour.RED);
            
            if (list != null && list.size() > 0) {
                
                for (int i = 0; i < list.size(); i++) {
                    
                    Object[] obj = (Object[])list.get(i);
                    for (int j = 0; j < obj.length - 1; j++) {
                        String value = (obj[j] == null ? "" : obj[j].toString());
                        Label label = null;
                        if (obj[obj.length - 1] != null) {
                            label = new Label(j, i + startRow + 1, value, format_cell_2);
                        } else {
                            label = new Label(j, i + startRow + 1, value, format_cell_1);
                        }
                        sheet.addCell(label);
                    }
                    sheet.setRowView(i + 2, 400); // 设置行的高度
                    
                }
                
            }
            wwb.write(); // 写入数据
        } catch (Exception e) {
            logger.error("ExcelUtils writeExcel case some error.", e);
        } finally {
            if (null != wwb) {
                try {
                    wwb.close();
                    wwb = null;
                } catch (WriteException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
}
